package com.ctshk.rpc.scenic.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.ctshk.rpc.scenic.dto.app.ScenicProductEsDTO;
import com.ctshk.rpc.scenic.dto.ScenicProductListDTO;
import com.ctshk.rpc.scenic.dto.ScenicProductPriceListDTO;
import com.ctshk.rpc.scenic.dto.ScenicReservationListDTO;
import com.ctshk.rpc.scenic.entity.ScenicProduct;
import com.ctshk.rpc.scenic.entity.ScenicProductCostStrategySpecStock;
import com.ctshk.rpc.scenic.req.ScenicProductPriceDetailReq;
import com.ctshk.rpc.scenic.req.ScenicProductPriceListReq;
import com.ctshk.rpc.scenic.req.ScenicReservationListReq;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 门票产品 Mapper 接口
 * </p>
 *
 * @author wangcy
 * @since 2021-02-02
 */
public interface ScenicProductMapper extends BaseMapper<ScenicProduct> {

    /**
     * 查询预订中心景点门票列表
     *
     * @param reservationListReq
     * @param page
     * @return
     */
    @Select("<script>"+"select tp.id id, tp.photos_json photosJson,tp.name name,tp.reserve_charge_type reserveChargeType,tp.reserve_charge reserveCharge,tp.theme_json themeJson,tp.city_id cityId,tp.city_name cityName, tp.scenic_id scenicId,s.currency_id currencyId,s.currency,s.exchange_rate exchangeRate from t_scenic_product tp   " +
            " inner join t_scenic_product_cost_strategy_vaild_date v on tp.id=v.product_id  and  " +
            " (DATE_FORMAT(v.time_begin,'%Y-%m-%d') = DATE_FORMAT(#{req.date},'%Y-%m-%d') or  " +
            " DATE_FORMAT(v.time_end,'%Y-%m-%d') = DATE_FORMAT(#{req.date},'%Y-%m-%d'))  " +
            " left join t_scenic_product_cost_strategy s on v.strategy_id = s.id  "+
            " where 1=1 and tp.allow_package != 3  " +
            "<if test=\"req.cityId != null\"> AND tp.city_id = #{req.cityId}</if>  " +
            "<if test=\"req.scenicId != null\"> AND tp.scenic_id = #{req.scenicId}</if>  " +
            "GROUP BY tp.id" +
            "</script>")
           /* "<if test=\"req.priceSort == 1\"> ORDER BY ts.cost_price</if>  " +
            "<if test=\"req.priceSort == 0 or req.priceSort == 2\"> ORDER BY ts.cost_price DESC</if>  " + "</script>")*/
    IPage<ScenicReservationListDTO> queryList(@Param("req") ScenicReservationListReq reservationListReq, IPage<ScenicReservationListDTO> page);

    /**
     *
     * @return
     */
    @Select("<script>"+"select tp.id id, tp.photos_json photosJson,tp.name name,tp.theme_json themeJson,tp.city_id cityId,tp.city_name cityName, tp.scenic_id scenicId, min(ts.cost_price) costPrice,tp.reserve_charge_type reserveChargeType,tp.reserve_charge reserveCharge,s.currency_id currencyId,s.currency,s.exchange_rate exchangeRate  from t_scenic_product tp  " +
            " inner join t_scenic_product_cost_strategy_vaild_date v on tp.id=v.product_id and   " +
            " (DATE_FORMAT(v.time_begin,'%Y-%m-%d') = DATE_FORMAT(#{time},'%Y-%m-%d') or  " +
            " DATE_FORMAT(v.time_end,'%Y-%m-%d') = DATE_FORMAT(#{time},'%Y-%m-%d'))  " +
            " inner join t_scenic_product_cost_strategy s on v.strategy_id = s.id  " +
            " inner join t_scenic_product_cost_strategy_spec_stock ts on tp.id = ts.product_id and ts.strategy_id=s.id  where 1=1 and tp.is_recommend = 1 and tp.allow_package != 3 GROUP BY tp.id  order by tp.gmt_create desc LIMIT 15" + "</script>")
    List<ScenicReservationListDTO> recommendedList(@Param("time") String time);

    /**
     * 查询景点门票各个规格的成本价
     *
     * @param scenicProductPriceListReq
     * @return
     */
    @Select("<script>"+"select p.name,s.cost_price  price,c.id  strategyId,c.name  strategyName,c.currency_id currencyId,c.exchange_rate exchangeRate,c.currency,s.attr1_id attr1Id,s.attr1_name attr1Name,s.attr2_id attr2Id,s.attr2_name attr2Name from  t_scenic_product p left join t_scenic_product_cost_strategy c " +
            "on p.id=c.product_id left join t_scenic_product_cost_strategy_spec_stock s on c.id=s.strategy_id and s.product_id=p.id left join t_scenic_product_cost_strategy_vaild_date v on c.id=v.strategy_id  and v.product_id=p.id " +
            "<if test=\"req.time !=null\"> AND DATE_FORMAT(v.time_begin,'%Y-%m-%d') = DATE_FORMAT(#{req.time},'%Y-%m-%d') or DATE_FORMAT(v.time_end,'%Y-%m-%d') = DATE_FORMAT(#{req.time},'%Y-%m-%d')</if> where 1=1 " +
            "<if test=\"req.productId !=0\"> AND p.id=#{req.productId}</if>  "
            + "</script>")
    List<ScenicProductPriceListDTO> scenicProductPriceList(@Param("req") ScenicProductPriceListReq scenicProductPriceListReq);

    /**
     * 查询景点门票单个规格的成本价
     *
     * @param scenicProductPriceDetailReq
     * @return
     */
    @Select("<script>"+"select p.name,s.cost_price  price,c.id  strategyId,c.name  strategyName,c.currency_id currencyId,c.exchange_rate exchangeRate,c.currency,s.attr1_id attr1Id,s.attr1_name attr1Name,s.attr2_id attr2Id,s.attr2_name attr2Name from  t_scenic_product p left join t_scenic_product_cost_strategy c " +
            "on p.id=c.product_id left join t_scenic_product_cost_strategy_spec_stock s on c.id=s.strategy_id and s.product_id=p.id left join t_scenic_product_cost_strategy_vaild_date v on c.id=v.strategy_id  and v.product_id=p.id " +
            "<if test=\"req.time !=null\"> AND DATE_FORMAT(v.time_begin,'%Y-%m-%d') = DATE_FORMAT(#{req.time},'%Y-%m-%d') or DATE_FORMAT(v.time_end,'%Y-%m-%d') = DATE_FORMAT(#{req.time},'%Y-%m-%d')</if> where 1=1 " +
            "<if test=\"req.productId !=null\"> AND p.id=#{req.productId}</if>  " +
            "<if test=\"req.attr1Id !=null\"> AND s.attr1_id=#{req.attr1Id}</if>  "+
            "<if test=\"req.attr1Name !=null\"> AND s.attr1_name=#{req.attr1Name}</if>  "+
            "<if test=\"req.attr2Id !=null\"> AND s.attr2_id=#{req.attr2Id}</if>  "+
            "<if test=\"req.attr2Name !=null\"> AND s.attr2_name=#{req.attr2Name}</if> Limit 1 "
            + "</script>")
    ScenicProductPriceListDTO scenicProductPriceDetail(@Param("req") ScenicProductPriceDetailReq scenicProductPriceDetailReq);

    @Select("SELECT usage_mode FROM t_scenic_product  WHERE id = #{id}")
    Integer queryUsageModeById(@Param("id")Long id);

    /**
     * 查询所有景点门票列表
     *
     * @param
     * @param
     * @return
     */
    @Select("<script>"+"select name,scenic_id,id,city_name cityName from  t_scenic_product where is_deleted = 0 and id in (${ids})  " +
            "</script>")
    List<ScenicProductListDTO> selectProductList(@Param("ids")String ids);

    /**
     * 查询当前用户的景点权限
     * 1.当前时间过滤增幅策略，成本策略
     * 2.查询线下开放所有部门
     * 3.指定开放的部门 或者人
     * @param reservationListReq
     * @param page
     * @return
     */
//    @Select("<script>"+" SELECT " +
//            "id id, photos_json photosJson,name name,reserve_charge_type reserveChargeType," +
//            "reserve_charge reserveCharge,theme_json themeJson,city_id cityId," +
//            "city_name cityName, scenic_id scenicId " +
//            " from t_scenic_product where id IN ( " +
//            " SELECT product_id from t_scenic_product_channel_offline  " +
//            " where  channel_type=1 AND   " +
//            " strategy_id IN ( " +
//            " SELECT strategy_id from t_scenic_product_sale_strategy_vaild_date where  " +
//            " DATE_FORMAT(time_begin,'%Y-%m-%d') &lt;= DATE_FORMAT(#{req.date},'%Y-%m-%d')  and  " +
//            " DATE_FORMAT(time_end,'%Y-%m-%d') &gt;= DATE_FORMAT(#{req.date},'%Y-%m-%d')  " +
//            ")  " +
//            " AND  product_id IN ( " +
//            " SELECT product_id from t_scenic_product_cost_strategy_vaild_date where  " +
//            " DATE_FORMAT(time_begin,'%Y-%m-%d') &lt;= DATE_FORMAT(#{req.date},'%Y-%m-%d')  and  " +
//            " DATE_FORMAT(time_end,'%Y-%m-%d') &gt;= DATE_FORMAT(#{req.date},'%Y-%m-%d')   " +
//            ")  " +
//            " AND  visable_type = 2   " +
//            " OR ( " +
//            " visable_type =3   " +
//            " AND  id IN ( " +
//            " SELECT offline_id from t_scenic_product_channel_offline_self_custom   " +
//            " where  ((check_type=1 and  relation_id = #{deptId})   " +
//            " OR (check_type=2 and  relation_id = #{userId}) ))   " +
//            ")) AND allow_package != 3 " +
//            "<if test=\"req.cityId != null\"> AND city_id = #{req.cityId}</if>  " +
//            "<if test=\"req.scenicId != null\"> AND scenic_id = #{req.scenicId}</if>  " +
//            "GROUP BY id  " +"</script>")


    @Select("<script>" +
            "SELECT  " +
            "tp.id id, tp.photos_json photosJson,tp.name name,tp.reserve_charge_type reserveChargeType,tp.reserve_charge reserveCharge, " +
            "tp.theme_json themeJson,tp.city_id cityId,tp.city_name cityName, tp.scenic_id scenicId, " +
            "ts.id offlineId from ( " +
            " SELECT * from t_scenic_product_channel_offline  ts " +
            " where  channel_type=1 and   " +
            " strategy_id in ( " +
            " SELECT strategy_id from t_scenic_product_sale_strategy_vaild_date where  " +
            " DATE_FORMAT(time_begin,'%Y-%m-%d') &lt;= DATE_FORMAT(#{req.date},'%Y-%m-%d')  and  " +
            " DATE_FORMAT(time_end,'%Y-%m-%d') &gt;= DATE_FORMAT(#{req.date},'%Y-%m-%d')  " +
            ")  " +
            " and  product_id in ( " +
            " SELECT product_id from t_scenic_product_cost_strategy_vaild_date where  " +
            " DATE_FORMAT(time_begin,'%Y-%m-%d') &lt;= DATE_FORMAT(#{req.date},'%Y-%m-%d')  and  " +
            " DATE_FORMAT(time_end,'%Y-%m-%d') &gt;= DATE_FORMAT(#{req.date},'%Y-%m-%d') )  " +
            ") ts  INNER JOIN t_scenic_product tp on ts.product_id =tp.id  " +
            "where  allow_package != 3 and tp.approval_status=3 " +
            "and ts.visable_type = 2  and tp.sale_status = 1" +
            "<if test=\"req.cityId != null\"> AND tp.city_id = #{req.cityId}</if>  " +
            "<if test=\"req.scenicId != null\"> AND tp.scenic_id = #{req.scenicId}</if>  " +
            "or" +
            " (ts.visable_type =3 and ts.id in ( SELECT offline_id from t_scenic_product_channel_offline_self_custom where " +
            " ((check_type=1 and relation_id = #{deptId}) or (check_type=2 and relation_id = #{userId})) ))  " +
            "order BY tp.id </script>")
    IPage<ScenicReservationListDTO> queryByList(@Param("req") ScenicReservationListReq reservationListReq,
                                                IPage<ScenicReservationListDTO> page,
                                                @Param("userId")Long  userId,
                                                @Param("deptId")Long  deptId);

    @Select("SELECT name From t_scenic_product Where supplier_id = #{supId} ")
    List<String> queryNameBySupplierId(@Param("supId")Long supplierId);

    @Select("<script>" +
            "SELECT  " +
            "tp.id id, tp.photos_json photosJson,tp.name name,tp.reserve_charge_type reserveChargeType,tp.reserve_charge reserveCharge, " +
            "tp.theme_json themeJson,tp.city_id cityId,tp.city_name cityName, tp.scenic_id scenicId, " +
            "ts.id offlineId from ( " +
            " SELECT * from t_scenic_product_channel_offline  ts " +
            " where  channel_type=1 and   " +
            " strategy_id in ( " +
            " SELECT strategy_id from t_scenic_product_sale_strategy_vaild_date where  " +
            " DATE_FORMAT(time_begin,'%Y-%m-%d') &lt;= DATE_FORMAT(#{date},'%Y-%m-%d')  and  " +
            " DATE_FORMAT(time_end,'%Y-%m-%d') &gt;= DATE_FORMAT(#{date},'%Y-%m-%d')  " +
            ")  " +
            " and  product_id in ( " +
            " SELECT product_id from t_scenic_product_cost_strategy_vaild_date where  " +
            " DATE_FORMAT(time_begin,'%Y-%m-%d') &lt;= DATE_FORMAT(#{date},'%Y-%m-%d')  and  " +
            " DATE_FORMAT(time_end,'%Y-%m-%d') &gt;= DATE_FORMAT(#{date},'%Y-%m-%d') )  " +
            ") ts  INNER JOIN t_scenic_product tp on ts.product_id =tp.id  " +
            "where  allow_package != 3  and tp.approval_status=3 " +
            "and ts.visable_type = 2 " +
            "and tp.is_recommend = 1  and tp.sale_status = 1 " +
            "or" +
            " (ts.visable_type =3 and ts.id in ( SELECT offline_id from t_scenic_product_channel_offline_self_custom where " +
            " ((check_type=1 and relation_id = #{deptId}) or (check_type=2 and relation_id = #{userId})) ))  " +
            "order by tp.gmt_create desc  limit 15 </script>")
    List<ScenicReservationListDTO> queryByList15(@Param("date") String date,
                                                @Param("userId")Long  userId,
                                                @Param("deptId")Long  deptId);

    List<ScenicProductListDTO> selectProductList();

    @Select("SELECT DISTINCT " +
            "t_scenic_product_channel_online_self_platform.channel_id AS channelId,  " +
            "t_scenic_product_channel_online_self_platform.channel_name AS channelName,  " +
            "t_scenic_product_cost_strategy_vaild_date.strategy_id AS strategyId,  " +
            "t_scenic_product_cost_strategy.`name` AS strategyName,  " +
            "t_scenic_product.id AS productId,  " +
            "t_scenic_product.`name` AS productName,  " +
            "t_scenic_product.is_recommend AS isRecommend,  " +
            "t_scenic_product.scenic_id AS scenicId,  " +
            "t_scenic_product.city_id AS cityId,  " +
            "t_scenic_product.city_name AS cityName,  " +
            "t_scenic_product.product_introduces AS productIntroduces,  " +
            "t_scenic_product.theme_json AS themeJson,  " +
            "t_scenic_product.photos_json AS photosJson,  " +
            "t_scenic_product.reserve_charge_type AS reserveChargeType,  " +
            "t_scenic_product.reserve_charge AS reserveCharge,  " +
            "t_scenic_product_channel_online_self_platform.adjust_type AS adjustType,  " +
            "t_scenic_product_channel_online_self_platform.amount AS amount,  " +
            "t_scenic_product_channel_online_self_platform.percent AS precent,  " +
            "t_scenic_product_cost_strategy_vaild_date.time_begin AS saleTimeBegin,  " +
            "t_scenic_product_cost_strategy_vaild_date.time_end AS saleTimeEnd,  " +
            "t_scenic_product.gmt_modified AS gmtModified,  " +
            "t_scenic_product.`status` AS `status`,  " +
            "t_scenic_product.sale_status AS saleStatus,  " +
            "t_scenic_product.is_deleted AS isDeleted,  " +
            "t_scenic_product_cost_strategy_spec_stock.cost_price AS costPrice,  " +
            "t_scenic_product_cost_strategy.exchange_rate AS exchangeRate,  " +
            "t_scenic_product_cost_strategy_spec_attr.spec_name AS attr2Name,  " +
            "t_scenic_product_cost_strategy_spec_attr.attribute_name AS attr1Name,  " +
            "t_scenic_product_cost_strategy_spec_stock.sold_stock AS soldStock,  " +
            "t_scenic_product.charge_user_id AS chargeUserId,  " +
            "t_scenic_product.charge_user AS chargeUser,  " +
            "t_scenic_product.supplier_id AS supplierId,  " +
            "t_scenic_product.supplier_name AS supplierName, " +
            "t_scenic_product.need_info AS needInfo, " +
            "t_scenic_product.admission_notes AS admissionNotes, " +
            "t_scenic_product.allow_refund AS allowRefund, " +
            "t_scenic_product.cover_image AS coverImage, " +
            "t_scenic_product.product_type_name AS productTypeName, " +
            "t_scenic_product_cost_strategy_spec_attr.id AS attrId, " +
            "t_scenic_product.vaild_day AS validDay, " +

            "t_scenic_product.vaild_type AS validType," +
            "t_scenic_product.vaild_day_begin AS validDayBegin," +
            "t_scenic_product.vaild_day_end AS validDayEnd," +

            "t_scenic_product.cancel_policy AS cancelPolicy " +
            "FROM " +
            "t_scenic_product_channel_online_self_platform, " +
            "t_scenic_product_cost_strategy_vaild_date, " +
            "t_scenic_product, " +
            "t_scenic_product_cost_strategy, " +
            "t_scenic_product_cost_strategy_spec_stock, " +
            "t_scenic_product_cost_strategy_spec_attr " +
            "WHERE " +
            "( " +
            "t_scenic_product_channel_online_self_platform.product_id = t_scenic_product_cost_strategy_vaild_date.product_id AND " +
            "t_scenic_product_channel_online_self_platform.product_id = t_scenic_product.id AND " +
            "t_scenic_product_channel_online_self_platform.product_id = t_scenic_product_cost_strategy.product_id AND " +
            "t_scenic_product_channel_online_self_platform.product_id = t_scenic_product_cost_strategy_spec_stock.product_id AND " +
            "t_scenic_product_channel_online_self_platform.product_id = t_scenic_product_cost_strategy_spec_attr.product_id " +
            ") AND " +
            "( " +
            "t_scenic_product_cost_strategy_vaild_date.strategy_id = t_scenic_product_cost_strategy_spec_stock.strategy_id AND " +
            "t_scenic_product_cost_strategy_vaild_date.strategy_id = t_scenic_product_cost_strategy.id " +
            ") AND " +
            "( " +
            "t_scenic_product.is_deleted = 0 AND " +
            "t_scenic_product_cost_strategy.is_deleted = 0 AND " +
            "t_scenic_product_cost_strategy_spec_stock.is_deleted = 0 AND " +
            "t_scenic_product_channel_online_self_platform.is_deleted = 0 " +
            ") " +
            "ORDER BY " +
            "isRecommend DESC,  " +
            "soldStock DESC,  " +
            "saleTimeEnd DESC")
    List<ScenicProductEsDTO> selectAllProductList();

    @Select("SELECT DISTINCT " +
            "t_scenic_product_channel_online_self_platform.channel_id AS channelId,  " +
            "t_scenic_product_channel_online_self_platform.channel_name AS channelName,  " +
            "t_scenic_product_cost_strategy_vaild_date.strategy_id AS strategyId,  " +
            "t_scenic_product_cost_strategy.`name` AS strategyName,  " +
            "t_scenic_product.id AS productId,  " +
            "t_scenic_product.`name` AS productName,  " +
            "t_scenic_product.is_recommend AS isRecommend,  " +
            "t_scenic_product.scenic_id AS scenicId,  " +
            "t_scenic_product.city_id AS cityId,  " +
            "t_scenic_product.city_name AS cityName,  " +
            "t_scenic_product.product_introduces AS productIntroduces,  " +
            "t_scenic_product.theme_json AS themeJson,  " +
            "t_scenic_product.photos_json AS photosJson,  " +
            "t_scenic_product.reserve_charge_type AS reserveChargeType,  " +
            "t_scenic_product.reserve_charge AS reserveCharge,  " +
            "t_scenic_product_channel_online_self_platform.adjust_type AS adjustType,  " +
            "t_scenic_product_channel_online_self_platform.amount AS amount,  " +
            "t_scenic_product_channel_online_self_platform.percent AS precent,  " +
            "t_scenic_product_cost_strategy_vaild_date.time_begin AS saleTimeBegin,  " +
            "t_scenic_product_cost_strategy_vaild_date.time_end AS saleTimeEnd,  " +
            "t_scenic_product.gmt_modified AS gmtModified,  " +
            "t_scenic_product.`status` AS `status`,  " +
            "t_scenic_product.sale_status AS saleStatus,  " +
            "t_scenic_product.is_deleted AS isDeleted,  " +
            "t_scenic_product_cost_strategy_spec_stock.cost_price AS costPrice,  " +
            "t_scenic_product_cost_strategy.exchange_rate AS exchangeRate,  " +
            "t_scenic_product_cost_strategy_spec_attr.spec_name AS attr2Name,  " +
            "t_scenic_product_cost_strategy_spec_attr.attribute_name AS attr1Name,  " +
            "t_scenic_product_cost_strategy_spec_stock.sold_stock AS soldStock,  " +
            "t_scenic_product.charge_user_id AS chargeUserId,  " +
            "t_scenic_product.charge_user AS chargeUser,  " +
            "t_scenic_product.supplier_id AS supplierId,  " +
            "t_scenic_product.supplier_name AS supplierName, " +
            "t_scenic_product.need_info AS needInfo, " +
            "t_scenic_product.admission_notes AS admissionNotes, " +
            "t_scenic_product.allow_refund AS allowRefund, " +
            "t_scenic_product.cover_image AS coverImage, " +
            "t_scenic_product.product_type_name AS productTypeName, " +
            "t_scenic_product_cost_strategy_spec_attr.id AS attrId, " +
            "t_scenic_product.vaild_day AS validDay, " +
            "t_scenic_product.cancel_policy AS cancelPolicy " +
            "FROM " +
            "t_scenic_product_channel_online_self_platform, " +
            "t_scenic_product_cost_strategy_vaild_date, " +
            "t_scenic_product, " +
            "t_scenic_product_cost_strategy, " +
            "t_scenic_product_cost_strategy_spec_stock, " +
            "t_scenic_product_cost_strategy_spec_attr " +
            "WHERE " +
            "( " +
            "t_scenic_product_channel_online_self_platform.product_id = t_scenic_product_cost_strategy_vaild_date.product_id AND " +
            "t_scenic_product_channel_online_self_platform.product_id = t_scenic_product.id AND " +
            "t_scenic_product_channel_online_self_platform.product_id = t_scenic_product_cost_strategy.product_id AND " +
            "t_scenic_product_channel_online_self_platform.product_id = t_scenic_product_cost_strategy_spec_stock.product_id AND " +
            "t_scenic_product_channel_online_self_platform.product_id = t_scenic_product_cost_strategy_spec_attr.product_id " +
            ") AND " +
            "( " +
            "t_scenic_product_cost_strategy_vaild_date.strategy_id = t_scenic_product_cost_strategy_spec_stock.strategy_id AND " +
            "t_scenic_product_cost_strategy_vaild_date.strategy_id = t_scenic_product_cost_strategy.id " +
            ") AND " +
            "( " +
            "t_scenic_product.is_deleted = 0 AND " +
            "t_scenic_product_cost_strategy.is_deleted = 0 AND " +
            "t_scenic_product_cost_strategy_spec_stock.is_deleted = 0 AND " +
            "t_scenic_product_channel_online_self_platform.is_deleted = 0 " +
            ") AND t_scenic_product.gmt_modified > #{gmtModified} " +
            "ORDER BY " +
            "isRecommend DESC,  " +
            "soldStock DESC,  " +
            "saleTimeEnd DESC")
    List<ScenicProductEsDTO> selectAllProductListByGmtModified(@Param("gmtModified") String gmtModified);

    @Select("<script> SELECT DISTINCT " +
            "t_scenic_product_channel_online_self_platform.channel_id AS channelId,  " +
            "t_scenic_product_channel_online_self_platform.channel_name AS channelName,  " +
            "t_scenic_product_cost_strategy_vaild_date.strategy_id AS strategyId,  " +
            "t_scenic_product_cost_strategy.`name` AS strategyName,  " +
            "t_scenic_product.id AS productId,  " +
            "t_scenic_product.`name` AS productName,  " +
            "t_scenic_product.is_recommend AS isRecommend,  " +
            "t_scenic_product.scenic_id AS scenicId,  " +
            "t_scenic_product.city_id AS cityId,  " +
            "t_scenic_product.city_name AS cityName,  " +
            "t_scenic_product.product_introduces AS productIntroduces,  " +
            "t_scenic_product.theme_json AS themeJson,  " +
            "t_scenic_product.photos_json AS photosJson,  " +
            "t_scenic_product.reserve_charge_type AS reserveChargeType,  " +
            "t_scenic_product.reserve_charge AS reserveCharge,  " +
            "t_scenic_product_channel_online_self_platform.adjust_type AS adjustType,  " +
            "t_scenic_product_channel_online_self_platform.amount AS amount,  " +
            "t_scenic_product_channel_online_self_platform.percent AS precent,  " +
            "t_scenic_product_cost_strategy_vaild_date.time_begin AS saleTimeBegin,  " +
            "t_scenic_product_cost_strategy_vaild_date.time_end AS saleTimeEnd,  " +
            "t_scenic_product.gmt_modified AS gmtModified,  " +
            "t_scenic_product.`status` AS `status`,  " +
            "t_scenic_product.sale_status AS saleStatus,  " +
            "t_scenic_product.is_deleted AS isDeleted,  " +
            "t_scenic_product_cost_strategy_spec_stock.cost_price AS costPrice,  " +
            "t_scenic_product_cost_strategy.exchange_rate AS exchangeRate,  " +
            "t_scenic_product_cost_strategy_spec_attr.spec_name AS attr2Name,  " +
            "t_scenic_product_cost_strategy_spec_attr.attribute_name AS attr1Name,  " +
            "t_scenic_product_cost_strategy_spec_stock.sold_stock AS soldStock,  " +
            "t_scenic_product.charge_user_id AS chargeUserId,  " +
            "t_scenic_product.charge_user AS chargeUser,  " +
            "t_scenic_product.supplier_id AS supplierId,  " +
            "t_scenic_product.supplier_name AS supplierName, " +
            "t_scenic_product.need_info AS needInfo, " +
            "t_scenic_product.admission_notes AS admissionNotes, " +
            "t_scenic_product.allow_refund AS allowRefund, " +
            "t_scenic_product.cover_image AS coverImage, " +
            "t_scenic_product.product_type_name AS productTypeName, " +
            "t_scenic_product_cost_strategy_spec_attr.id AS attrId, " +
            "t_scenic_product.vaild_day AS validDay, " +

            "t_scenic_product.vaild_type AS validType," +
            "t_scenic_product.vaild_day_begin AS validDayBegin," +
            "t_scenic_product.vaild_day_end AS validDayEnd," +

            "t_scenic_product.cancel_policy AS cancelPolicy " +
            "FROM " +
            "t_scenic_product_channel_online_self_platform, " +
            "t_scenic_product_cost_strategy_vaild_date, " +
            "t_scenic_product, " +
            "t_scenic_product_cost_strategy, " +
            "t_scenic_product_cost_strategy_spec_stock, " +
            "t_scenic_product_cost_strategy_spec_attr " +
            "WHERE " +
            "( " +
            "t_scenic_product_channel_online_self_platform.product_id = t_scenic_product_cost_strategy_vaild_date.product_id AND " +
            "t_scenic_product_channel_online_self_platform.product_id = t_scenic_product.id AND " +
            "t_scenic_product_channel_online_self_platform.product_id = t_scenic_product_cost_strategy.product_id AND " +
            "t_scenic_product_channel_online_self_platform.product_id = t_scenic_product_cost_strategy_spec_stock.product_id AND " +
            "t_scenic_product_channel_online_self_platform.product_id = t_scenic_product_cost_strategy_spec_attr.product_id " +
            ") AND " +
            "( " +
            "t_scenic_product_cost_strategy_vaild_date.strategy_id = t_scenic_product_cost_strategy_spec_stock.strategy_id AND " +
            "t_scenic_product_cost_strategy_vaild_date.strategy_id = t_scenic_product_cost_strategy.id " +
            ") AND " +
            "( " +
            "t_scenic_product.is_deleted = 0 AND " +
            "t_scenic_product_cost_strategy.is_deleted = 0 AND " +
            "t_scenic_product_cost_strategy_spec_stock.is_deleted = 0 AND " +
            "t_scenic_product_channel_online_self_platform.is_deleted = 0 " +
            ") AND t_scenic_product.id in <foreach collection=\"ids\" item=\"id\" separator=\",\"\n" +
            "        open=\"(\" close=\")\">" +
            "        #{id}" +
            "        </foreach> "  +
            "ORDER BY " +
            "isRecommend DESC,  " +
            "soldStock DESC,  " +
            "saleTimeEnd DESC </script>")
    List<ScenicProductEsDTO> selectAllProductListByids(@Param("ids")List<Long> ids);


    @Select("select * from t_scenic_product where product_id = #{scenicId} and is_deleted = 0")
    ScenicProduct quertByProductId(@Param("scenicId") String scenicId);
}










